using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace GeneratorLibrary.Client.Base {
    public abstract class CrudTableBase {
        public abstract CrudColumnBase NewColumn(DataRow dr);
        protected abstract ProcLocation GetWhereToPutComments();
        protected abstract void WriteGrant(string storedProcedure);
        protected abstract void WriteComments(StringBuilder sb, string description);
        protected abstract bool DeclareColumn(StringBuilder sb, CrudColumnBase c, bool first, bool output, bool setToNull);
        protected abstract void EndMethodBody(StringBuilder sb, bool hasResultset);
        protected abstract void StartMethodBody(StringBuilder sb, bool hasResultset);
        protected abstract void StartMethodBody(StringBuilder sb, bool hasResultset, string commentsDescription);
        protected abstract void PrintTablePrefix(StringBuilder sb);
        protected abstract void CreateProcedureDeclaration(StringBuilder sb, string procName);
        protected abstract void SelectIdentityColumn(StringBuilder sb, string identityName);
        protected abstract void CloseCRUDStatement(StringBuilder sb);
        protected abstract string GetParameterPrefix(bool isOutput);

        #region Member variables
        protected string tableName = string.Empty;
        protected string author = string.Empty;
        protected string isActive = string.Empty;
        protected string grantAccess = string.Empty;
        protected string currentGrantSql = string.Empty;
        protected List<CrudColumnBase> columns = new List<CrudColumnBase>();
        protected string[] orderBy;
        protected string database = string.Empty;
        protected string procDatabase = string.Empty;
        #endregion

        #region Properties
        public string Author {
            get { return author; }
            set { author = value; }
        }
        public string IsActive {
            get { return isActive; }
            set { isActive = value; }
        }
        public string TableName {
            get { return tableName; }
            set { tableName = value; }
        }
        public List<CrudColumnBase> Columns {
            get { return columns; }
            set { columns = value; }
        }
        public string OrderBy {
            get {
                return string.Join(",", orderBy);
            }
            set { 
                orderBy = value.Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries); 
            }
        }

        public string GrantAccess {
            get { return grantAccess; }
            set { grantAccess = value; }
        }

        public string CurrentGrantSql {
            get { return currentGrantSql; }
        }

        public string ProcDatabase {
            get { return procDatabase; }
            set { procDatabase = value; }
        }
        public string Database {
            get { return database; }
            set { database = value; }
        }
        #endregion

        #region Column Selector Methods

        public List<CrudColumnBase> GetPrimaryKeys() {
            List<CrudColumnBase> list = new List<CrudColumnBase>();
            foreach (CrudColumnBase column in columns) {
                if (column.IsPrimaryKey)
                    list.Add(column);
            }
            return list;
        }
        public List<CrudColumnBase> GetKeysAndConstraints() {
            List<CrudColumnBase> list = new List<CrudColumnBase>();
            foreach (CrudColumnBase column in columns) {
                if (column.IsPrimaryKey || column.IsForeignKey || column.IsUniqueConstraint)
                    list.Add(column);
            }
            return list;
        }
        public List<CrudColumnBase> GetNotPrimaryKeysAndNotIdentity() {
            List<CrudColumnBase> list = new List<CrudColumnBase>();
            foreach (CrudColumnBase column in columns) {
                if (!column.IsPrimaryKey && !column.IsIdentity)
                    list.Add(column);
            }
            return list;
        }
        public List<CrudColumnBase> GetAllColumns() {
            return columns;
        }
        public CrudColumnBase GetIdentityColumn() {
            foreach (CrudColumnBase column in columns) {
                if (column.IsIdentity)
                    return column;
            }
            return null;
        }
        public List<CrudColumnBase> GetNotIdentity() {
            List<CrudColumnBase> list = new List<CrudColumnBase>();
            foreach (CrudColumnBase column in columns) {
                if (!column.IsIdentity)
                    list.Add(column);
            }
            return list;
        } 
        #endregion

        #region CRUD Methods
        internal string GenerateRead() {
            StringBuilder sb = new StringBuilder(2000);

            ProcLocation l = GetWhereToPutComments();
            string comments = "Reads record(s) from " + tableName + ". These 'may' be limited by primary/forign keys or an IsActive column.";

            if (l == ProcLocation.BeforeProcedureDeclaration)
                WriteComments(sb, comments);

            CreateProcedureDeclaration(sb, this.tableName + "_Read");
            
            if (l == ProcLocation.BeforeProcedureParameters)
                WriteComments(sb, comments);

            CrudColumnBase isActiveColumn;
            bool hasIsActiveColumn = HasIsActiveColumn(out isActiveColumn);
            string testIsActiveColumnName = "";
            if (hasIsActiveColumn)
                testIsActiveColumnName = isActiveColumn.Name;

            List<CrudColumnBase> parameters = new List<CrudColumnBase>();
            
            foreach (CrudColumnBase column in columns) {
                if (column.IsPrimaryKey || column.IsForeignKey || column.IsUniqueConstraint || column.Name == testIsActiveColumnName)
                    parameters.Add(column);
            }

            DeclareColumnList(sb, parameters, true, true);

            if (l == ProcLocation.BeforeMethodBody)
                StartMethodBody(sb, true, comments);
            else
                StartMethodBody(sb, true);

            sb.Append("\tselect\r\n\t\t");

            SelectColumns(sb, true, this.Columns);

            sb.Append("\r\n\tfrom ");
            PrintTablePrefix(sb);

            sb.Append(this.tableName);

            KeysAndActiveCoalesce_WhereClause(sb, isActiveColumn);

            sb.Append("\r\n");
            WriteOrderBy(sb, true);

            CloseCRUDStatement(sb);

            sb.Append("\r\n");

            EndMethodBody(sb, true);

            WriteGrant("_Read");

            return sb.ToString();
        }

        internal string GenerateUpdate() {
            StringBuilder sb = new StringBuilder(2000);


            ProcLocation l = GetWhereToPutComments();
            string comments = "Updates a single record from " + tableName;

            if (l == ProcLocation.BeforeProcedureDeclaration)
                WriteComments(sb, comments);

            CreateProcedureDeclaration(sb, this.tableName + "_Update");

            if (l == ProcLocation.BeforeProcedureParameters)
                WriteComments(sb, comments);

            DeclareColumnList(sb, this.columns, true, true);
            //first = DeclareColumnList(sb, GetPrimaryKeys(), true);
            //DeclareColumnList(sb, notPrimaryOrIdentity, first);

            if (l == ProcLocation.BeforeMethodBody)
                StartMethodBody(sb, false, comments);
            else
                StartMethodBody(sb, false);

            sb.Append("\tupdate ");

            PrintTablePrefix(sb);
            sb.Append(this.tableName);

            sb.Append("\r\n\tset\r\n");
            bool first = true;
            List<CrudColumnBase> notPrimaryOrIdentity = GetNotPrimaryKeysAndNotIdentity();
            foreach (CrudColumnBase c in notPrimaryOrIdentity) {
                if (!first) {
                    sb.Append(",\r\n\t\t");
                } else {
                    first = !first;
                    sb.Append("\t\t");
                }
                sb.Append(c.Name);
                sb.Append(" = ");
                sb.Append(GetParameterPrefix(false));
                sb.Append(c.Name);
            }

            PK_WhereClause(sb);

            CloseCRUDStatement(sb);

            EndMethodBody(sb, false);

            WriteGrant("_Update");

            return sb.ToString();
        }

        internal string GenerateDelete() {
            StringBuilder sb = new StringBuilder(2000);

            bool hasActive = HasIsActiveColumn();

            ProcLocation l = GetWhereToPutComments();
            string comments = (hasActive ? "Deactivates" : "Deletes") + " a single record from " + tableName;

            if (l == ProcLocation.BeforeProcedureDeclaration)
                WriteComments(sb, comments);

            CreateProcedureDeclaration(sb, this.tableName + "_Delete");

            if (l == ProcLocation.BeforeProcedureParameters)
                WriteComments(sb, comments);

            DeclareColumnList(sb, GetPrimaryKeys(), true);

            if (l == ProcLocation.BeforeMethodBody)
                StartMethodBody(sb, false, comments);
            else
                StartMethodBody(sb, false);

            if (hasActive) {
                sb.Append("\tupdate ");

                PrintTablePrefix(sb);
                sb.Append(this.tableName);

                sb.Append("\r\n\tset\r\n\t\t");

                sb.Append(this.IsActive);

                sb.Append(" = 0");

                PK_WhereClause(sb);
            } else {
                sb.Append("\tdelete from ");

                PrintTablePrefix(sb);
                sb.Append(this.tableName);

                PK_WhereClause(sb);
            }
            CloseCRUDStatement(sb);

            EndMethodBody(sb, false);

            WriteGrant("_Delete");

            return sb.ToString();
        }

        internal string GenerateCreate() {
            StringBuilder sb = new StringBuilder(2000);

            ProcLocation l = GetWhereToPutComments();
            string comments = "Inserts a single record into " + tableName;


            if (l == ProcLocation.BeforeProcedureDeclaration)
                WriteComments(sb, comments);

            CreateProcedureDeclaration(sb, this.tableName + "_Create");

            if (l == ProcLocation.BeforeProcedureParameters)
                WriteComments(sb, comments);

            DeclareColumnList(sb, this.columns, true, true, true);

            List<CrudColumnBase> nonIdentity = GetNotIdentity();

            bool first;

            if (l == ProcLocation.BeforeMethodBody)
                StartMethodBody(sb, false, comments);
            else
                StartMethodBody(sb, false);

            sb.Append("\tinsert into ");
            PrintTablePrefix(sb);
            sb.Append(this.tableName);
            sb.Append("\r\n\t\t(");

            SelectColumns(sb, true, nonIdentity);

            sb.Append(")\r\n\tvalues\r\n\t\t(");
            first = true;
            foreach (CrudColumnBase c in nonIdentity) {
                if (!first) {
                    sb.Append(",");
                    sb.Append(GetParameterPrefix(false));
                } else {
                    first = !first;
                    sb.Append(GetParameterPrefix(false));
                }
                sb.Append(c.Name);
            }
            sb.Append(")");
            
            CloseCRUDStatement(sb);

            foreach (CrudColumnBase c in this.columns) {
                if (c.IsIdentity && c.IsPrimaryKey) {
                    SelectIdentityColumn(sb, c.Name);
                    break;
                }
            }
            EndMethodBody(sb, false);

            WriteGrant("_Create");

            return sb.ToString();
        }
        #endregion

        #region CRUD - Common Code Methods

        private bool HasIsActiveColumn() {
            CrudColumnBase c;
            return HasIsActiveColumn(out c);
        }
        private bool HasIsActiveColumn(out CrudColumnBase column) {
            foreach (CrudColumnBase c in this.columns) {
                //IsActive Column Name = IsActive, PersonIsActive, Person_IsActive
                string columnTest = c.Name.ToUpper();
                if (columnTest.StartsWith(tableName.ToUpper())) {
                    columnTest = columnTest.Remove(0, tableName.Length);
                    if (columnTest.StartsWith("_"))
                        columnTest.Remove(0, 1);
                }
                if (columnTest == this.isActive.ToUpper()) {
                    column = c;
                    return true;
                }
            }
            column = null;
            return false;
        }
        private void WriteOrderBy(StringBuilder sb, bool orderByIsActive) {
            string orderBy = "";
            if (orderByIsActive && HasIsActiveColumn()) {
                orderBy += this.isActive + " desc, ";
            }

            //order by SortOrder / Name / Label
            //tests prefix of table name: if Person table, order by "PersonName"
            //tests prefix of table name witn underscore: "Person_Name"

            foreach (string name in this.orderBy) {
                foreach (CrudColumnBase c in this.columns) {
                    string columnTest = c.Name.ToUpper();
                    if (columnTest.StartsWith(tableName.ToUpper())) {
                        columnTest = columnTest.Remove(0, tableName.Length);
                        if (columnTest.StartsWith("_"))
                            columnTest.Remove(0, 1);
                    }
                    if (c.Name.ToUpper() == name.ToUpper().Trim()) {
                        orderBy += name.Trim() + ", ";
                    }
                }
            }
            if (orderBy.Length > 0) {
                orderBy = orderBy.Remove(orderBy.Length - 2);//remove last comma/space
                sb.Append("\torder by ");
                sb.Append(orderBy);
            }
        }
        

        private void KeysAndActiveCoalesce_WhereClause(StringBuilder sb, CrudColumnBase isActiveColumn) {
            List<CrudColumnBase> constraints = GetKeysAndConstraints();
            bool hasKeys = constraints.Count > 0;

            if (hasKeys || isActiveColumn != null) {
                sb.Append("\r\n\twhere\r\n");
                bool first = true;
                foreach (CrudColumnBase c in constraints) {
                    first = CoalesceWhereColumn(sb, first, c);
                }
                if (isActiveColumn != null) {
                    first = CoalesceWhereColumn(sb, first, isActiveColumn);
                }
            }
        }

        private bool CoalesceWhereColumn(StringBuilder sb, bool first, CrudColumnBase c) {
            if (!first) {
                sb.Append("\r\n\t\tand ");
            } else {
                first = !first;
                sb.Append("\t\t");
            }
            if (c.IsNullable) {
                sb.Append("coalesce(");
                sb.Append(c.Name);
                sb.Append(",");
                sb.Append(c.GetDefaultCoalesce());

                sb.Append(") = coalesce(");
                sb.Append(GetParameterPrefix(false));
                sb.Append(c.Name);
                sb.Append(", ");
                sb.Append(c.Name);
                sb.Append(",");
                sb.Append(c.GetDefaultCoalesce());
                sb.Append(")");
            } else {
                sb.Append(c.Name);
                sb.Append(" = coalesce(");
                sb.Append(GetParameterPrefix(false));
                sb.Append(c.Name);
                sb.Append(", ");
                sb.Append(c.Name);
                sb.Append(")");
            }
            return first;
        }

        private void PK_WhereClause(StringBuilder sb) {
            sb.Append("\r\n\twhere\r\n");
            bool first = true;
            foreach (CrudColumnBase c in GetPrimaryKeys()) {
                if (!first) {
                    sb.Append("\r\n\t\tand ");
                } else {
                    first = !first;
                    sb.Append("\t\t");
                }
                sb.Append(c.Name);
                sb.Append(" = ");
                sb.Append(GetParameterPrefix(false));
                sb.Append(c.Name);
            }
        }


        private bool DeclareColumnList(StringBuilder sb, List<CrudColumnBase> columns, bool first, bool setAllToNull) {
            return DeclareColumnList(sb, columns, first, setAllToNull, false);
        }

        private bool DeclareColumnList(StringBuilder sb, List<CrudColumnBase> columns, bool first, bool setAllToNull, bool setIdentityToOutput) {
            foreach (CrudColumnBase c in columns) {
                bool output = setIdentityToOutput && c.IsIdentity;
                first = DeclareColumn(sb, c, first, output, setAllToNull);
            }
            return first;
        }

        private bool DeclareColumnList(StringBuilder sb, List<CrudColumnBase> columns, bool first) {
            return DeclareColumnList(sb, columns, first, false);
        }
        private bool DeclareColumn(StringBuilder sb, CrudColumnBase c, bool first, bool output) {
            return DeclareColumn(sb, c, first, output, false);
        }
        
        private bool SelectColumns(StringBuilder sb, bool first, List<CrudColumnBase> nonIdentity) {
            foreach (CrudColumnBase c in nonIdentity) {
                if (!first) {
                    sb.Append(", ");
                } else {
                    first = !first;
                }
                sb.Append(c.Name);
            }
            return first;
        }
        #endregion
    }
}
